﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ANZ_AIM_DAL;
using System.Globalization;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Diagnostics;

namespace ANZ_AIM_BLL
{
    public class EmploymentOfflineBLL
    {
        private const string TABLE_NAME = "EmploymentOffline";
        private const string TABLE_NAME_Data_Change_Temp = "EmploymentOfflineChangeTemp";
        private string ConnectionString;
        private Dictionary<string, string> ObjectPropDict;

        public EmploymentOfflineBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public bool Save(List<EmploymentOffline> employmentOfflineList)
        {
            bool flag = true;
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                var dictUpdate = MiscHelper.Clone(this.ObjectPropDict);
                dictUpdate.Remove("ProfileID");
                dictUpdate.Remove("ID");
                OleDbParameter[] parametersInsert;
                OleDbParameter[] parametersUpdate;
                OleDbParameter[] parametersCheckExist = new OleDbParameter[1];
                string command = string.Empty;
                string commandCheckExist = string.Format("SELECT COUNT(ID) FROM [{0}] WHERE ProfileID=@ProfileID", TABLE_NAME);
                foreach (EmploymentOffline employment in employmentOfflineList)
                {

                    parametersCheckExist[0] = new OleDbParameter("@ProfileID", employment.ProfileID);
                    int checkExists = (int)OleDbHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, commandCheckExist, parametersCheckExist);
                    if (checkExists > 0)
                    {
                        var conditionDict = new Dictionary<string, string>
                        {
                            {"ProfileID", "ProfileID"}
                        };
                        command = CommandHelper.BuildUpdateCommand(employment, dictUpdate, conditionDict, out parametersUpdate);
                        flag = OleDbHelper.ExecuteNonQueryWithRollback(this.ConnectionString, CommandType.Text, command, parametersUpdate);
                    }
                    else
                    {
                        command = CommandHelper.BuildInsertCommand(employment, dict, out parametersInsert);
                        flag = OleDbHelper.ExecuteNonQueryWithRollback(this.ConnectionString, CommandType.Text, command, parametersInsert);
                    }
                }
            }
            catch (Exception ex) { throw; }
            return flag;
        }
        public bool Update(DataTable dtChange, List<string> profileIDList, List<string> batchFileList)
        {
            bool flag = true;
            try
            {
                OleDbParameter[] parameters;
                string command = "";
                //update by profileID
                foreach (string profileID in profileIDList)
                {
                    EmploymentOffline eo = new EmploymentOffline();
                    eo.ProfileID = profileID;
                    var dict = new Dictionary<string, string>();
                    var condition = new Dictionary<string, string>();
                    DataRow[] foundRows = dtChange.Select("[Profile ID]= '" + profileID + "'");
                    foreach (DataRow row in foundRows)
                    {
                        string fieldChange = row["Field Change"].ToString();
                        string newValue = row["New Value"].ToString();

                        dict.Add(fieldChange, fieldChange);

                        if (!eo.GetType().GetProperty(fieldChange).PropertyType.Name.ToString().ToUpper().Equals("STRING"))
                        {
                            eo.GetType().GetProperty(fieldChange).SetValue(eo, MiscHelper.StringToDate(newValue, new string[] { "MM/dd/yyyy", "M/d/yyyy", "MM/d/yyyy", "M/dd/yyyy" }), null);
                        }
                        else
                        {
                            eo.GetType().GetProperty(fieldChange).SetValue(eo, newValue, null);
                        }
                    }
                    condition.Add("ProfileID", "ProfileID");
                    command = CommandHelper.BuildUpdateCommand(eo, dict, condition, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }

                //update by batchFile
                command = "";
                foreach (string batchFile in batchFileList)
                {
                    EmploymentOffline eo = new EmploymentOffline();
                    eo.BatchFileID = batchFile;
                    var dict = new Dictionary<string, string>();
                    var condition = new Dictionary<string, string>();
                    DataRow[] foundRows = dtChange.Select("[Batch File]= '" + batchFile + "'");
                    foreach (DataRow row in foundRows)
                    {
                        string fieldChange = row["Field Change"].ToString();
                        string newValue = row["New Value"].ToString();

                        dict.Add(fieldChange, fieldChange);
                        eo.GetType().GetProperty(fieldChange).SetValue(eo, newValue, null);
                    }
                    condition.Add("BatchFileID", "BatchFileID");
                    command = CommandHelper.BuildUpdateCommand(eo, dict, condition, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
            return flag;
        }
        public bool SaveChangeTemp(DataTable dtDataChange)
        {
            bool flag = true;
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters = new OleDbParameter[6];
                string command = string.Empty;

                string SQLString = string.Format("DELETE FROM [{0}] WHERE 1=1", TABLE_NAME_Data_Change_Temp);
                OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, SQLString);

                foreach (DataRow row in dtDataChange.Rows)
                {
                    command = "INSERT INTO " + TABLE_NAME_Data_Change_Temp + "(ProfileID, BatchFile, FieldChange, OldValue, NewValue, FirstValue) VALUES (@ProfileID, @BatchFile, @FieldChange, @OldValue, @NewValue, @FirstValue)";
                    parameters[0] = new OleDbParameter("@ProfileID", row["ProfileID"] != null ? row["ProfileID"].ToString() : "");
                    parameters[1] = new OleDbParameter("@BatchFile", row["BatchFile"] != null ? row["BatchFile"].ToString() : "");
                    parameters[2] = new OleDbParameter("@FieldChange", row["FieldChange"].ToString());
                    parameters[3] = new OleDbParameter("@OldValue", row["OldValue"].ToString());
                    parameters[4] = new OleDbParameter("@NewValue", row["NewValue"].ToString());
                    parameters[5] = new OleDbParameter("@FirstValue", row["FirstValue"].ToString());

                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
            return flag;
        }
        public DataTable GetEmploymentOfflines()
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public DataTable GetEmploymentOfflineByBatchFile(string batchFile)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                command += " WHERE BatchFileID=@batchFile";
                OleDbParameter[] parameters = new OleDbParameter[1];
                parameters[0] = new OleDbParameter("@batchFile", batchFile);

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public DataTable GetDataChangeTemp()
        {
            try
            {
                string command = string.Empty;
                command = string.Format("SELECT ProfileID, BatchFile, FieldChange, OldValue, NewValue, FirstValue FROM [{0}]", TABLE_NAME_Data_Change_Temp);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public DataTable Search(string saleCode, string FullName, string profileID, string batchFile)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                command += " WHERE (@saleCode IS NULL OR CurrentSalesCode LIKE '%' +  @saleCode + '%')";
                command +=" AND (@FullName IS NULL OR EmployeeFullNameE LIKE '%' + @FullName + '%')";
                command += "AND (@profileID IS NULL OR ProfileID LIKE '%' + @profileID + '%') AND (@batchFile IS NULL OR BatchFileID=@batchFile)";

                OleDbParameter[] parameters = new OleDbParameter[4];

                if (string.IsNullOrEmpty(saleCode))
                {
                    parameters[0] = new OleDbParameter("@saleCode", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@saleCode", saleCode);
                }

                if (string.IsNullOrEmpty(FullName))
                {
                    parameters[1] = new OleDbParameter("@FullName", DBNull.Value);
                }
                else
                {
                    parameters[1] = new OleDbParameter("@FullName", FullName);
                }

                if (string.IsNullOrEmpty(profileID))
                {
                    parameters[2] = new OleDbParameter("@profileID", DBNull.Value);
                }
                else
                {

                    parameters[2] = new OleDbParameter("@profileID", profileID);
                }

                if (string.IsNullOrEmpty(batchFile))
                {
                    parameters[3] = new OleDbParameter("@batchFile", DBNull.Value);
                }
                else
                {

                    parameters[3] = new OleDbParameter("@batchFile", batchFile);
                }

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {

            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"ID", "ID"},
                {"ProfileID", "ProfileID"},
                {"CandidateCode", "CandidateCode"},
                {"CurrentSalesCode", "CurrentSalesCode"},
                {"EmployeeFullName", "EmployeeFullName"},
                {"EmployeeFullNameE", "EmployeeFullNameE"},
                {"Role", "Role"},
                {"Level", "Level"},
                {"Category", "Category"},
                {"ProductCategory", "ProductCategory"},
                {"Product", "Product"},
                {"TeamName", "TeamName"},
                {"TeamLeaderCode", "TeamLeaderCode"},
                {"Channel", "Channel"},
                {"JoiningDate", "JoiningDate"},
                {"PromotionTransferDate", "PromotionTransferDate"},
                {"Status", "Status"},
                {"ContractType", "ContractType"},
                {"ContractTerm", "ContractTerm"},
                {"ContractorName", "ContractorName"},
                {"ContractSignDate", "ContractSignDate"},
                {"ContractExpiryDate", "ContractExpiryDate"},
                {"BatchFileID", "BatchFileID"}
            };

            return dictionary;
        }
    }
}
